Reorganization of tables (Unpivot) in LibreOffice Calc

Description

Sometimes there is a need to convert a cross (pivot) table into a flat one. This procedure is called " data straightening " (unpivot data) . As a result of such " straightening " we get a table where all similar data are in one column.

The program LibreOffice Calc does not have a standard functionality for converting pivot tables to " flat ", but you can write the corresponding procedure yourself. Let's see how to do it.

Let's look at the picture below to understand the essence of the problem more clearly. On the left we have a table in the form of a matrix (pivot table), and on the right - a regular table with columns (flat table):

When you expand a table, you unpack the attribute-value pair that is the intersection of the columns in the matrix table and reorient them into the flattened columns in the flat table:

StarBASIC code for the UnPivotTable procedure

Open the menu Tools - Macros - Edit Macros..., select Module1 and copy the following text into the module:

Sub UnPivotTable
  ' moonexcel.com.ua 
  Dim oBook        As Object
  Dim oActiveSheet As Object
  Dim oSelRange    As Object
  Dim oNewSheet    As Object
  
  Dim i                  As Long
  Dim iTopLabelRowCount  As Integer
  Dim iSideLabelColCount As Integer       
  
  oBook        = ThisComponent
  oActiveSheet = oBook.CurrentController.ActiveSheet
  oSelRange    = oBook.CurrentSelection
  
  ' Add a new sheet 
  oBook.Sheets.insertNewByName("UnPivoted Table",0)
  oNewSheet = oBook.Sheets(0)
  
  ' Determine the size of the headers 
  iTopLabelRowCount  = InputBox(" How many lines in the top header? ")
  iSideLabelColCount = InputBox(" How many columns in the side header? ")
            
  i = 0
     
  For r = (iTopLabelRowCount + 1) To oSelRange.Rows.Count
    For c = (iSideLabelColCount + 1) To oSelRange.Columns.Count            
                       
      For j = 1 To iSideLabelColCount              
        oNewSheet.getCellByPosition(j-1, i).Formula = oSelRange.getCellByPosition(j-1, r-1).Formula               
      Next j
             
      For k = 1 To iTopLabelRowCount
        oNewSheet.getCellByPosition(j + k - 2, i).Formula = oSelRange.getCellByPosition(c-1, k-1).Formula
      Next k           
                         
      oNewSheet.getCellByPosition(j + k - 2, i).Formula = oSelRange.getCellByPosition(c-1, r-1).Formula
      i = i + 1                   
            
    Next c
  Next r
End Sub

Then, close Macro Editor and return to your worksheet in LibreOffice Calc. Select the entire table with the top and left headers and run our new macro via the menu Tools - Macros - Run Macro...

The macro will insert a new named sheet into your workbook "UnPivoted Table" , and then add a table in the new format. With such a table you can do any analysis in LibreOffice Calc.

Using the extension

You can also use the utility "Pivot table (Unpivot)" by installing the extension YLC_Utilities.oxt .

After that, this utility will be available in all files that will be opened in LibreOffice Calc.

Related Articles: